![🐼](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="72" height="72"><rect fill-opacity="0"/></svg> "Panda face")
pandas trick:
5 useful "read_csv" parameters that are often overlooked: names: specify column names usecols: which columns to keep dtype: specify data types nrows: # of rows to read na_values: strings to recognize as NaN#Python #DataScience #pandastricks
Master Python's pandas library with these 100 tricks
Below you'll find 100 tricks that will save you time and energy every time you use pandas! These the best tricks I've learned from 5 years of teaching the pandas library.
"Soooo many nifty little tips that will make my life so much easier!" - C.K.
"Kevin, these tips are so practical. I can say without hesitation that you provide the best resources for pandas I have ever used." - N.W.
P.S. You can also watch a video of my top 25 tricks! 🐼🤹
Categories
- Reading files
- Reading from the web
- Creating example DataFrames
- Creating columns
- Renaming columns
- Selecting rows and columns
- Filtering rows by condition
- Manipulating strings
- Working with data types
- Encoding data
- Extracting data from lists
- Working with time series data
- Handling missing values
- Using aggregation functions
- Using cumulative functions
- Random sampling
- Merging DataFrames
- Styling DataFrames
- Exploring a dataset
- Handling warnings
- Other
Reading files
35 people are talking about this
Got bad data (or empty rows) at the top of your CSV file? Use these read_csv parameters:
See example
19 people are talking about this
Two easy ways to reduce DataFrame memory usage:
1. Only read in columns you need
2. Use 'category' data type with categorical data.
Example:
df = http://pd.read _csv('file.csv', usecols=['A', 'C', 'D'], dtype={'D':'category'})#Python #pandastricks
21 people are talking about this
You can read directly from a compressed file:
df = http://pd.read _csv('https://t.co/3JAwA8h7FJ')
Or write to a compressed file:http://df.to _csv('https://t.co/3JAwA8h7FJ')
Also supported: .gz, .bz2, .xz#Python #pandas #pandastricks
33 people are talking about this
Do you sometimes end up with an "Unnamed: 0" column in your DataFrame?
Solution: Set the first column as the index (when reading)
Alternative: Don't save the index to the file (when writing)
See example
23 people are talking about this
Are your dataset rows spread across multiple files, but you need a single DataFrame?
Solution:
1. Use glob() to list your files
2. Use a generator expression to read files and concat() to combine them
3.
See example
82 people are talking about this
Do you need to build a DataFrame from multiple files, but also keep track of which row came from which file?
1. List files w/ glob()
2. Read files w/ gen expression, create new column w/ assign(), combine w/ concat()
See example
51 people are talking about this
Want to read a HUGE dataset into pandas but don't have enough memory?
Randomly sample the dataset *during file reading* by passing a function to "skiprows"
See example
Thanks to @TedPetrou for this trick!
134 people are talking about this
Need to quickly get data from Excel or Google Sheets into pandas?
1. Copy data to clipboard
2. df = http://pd.read _clipboard()
3.
See example
Learn 25 more tips & tricks: https://www.youtube.com/watch?v=RlIiVeig3hc …#Python #DataScience #pandas #pandastricks
114 people are talking about this
Want to extract tables from a PDF into a DataFrame? Try tabula-py!
from tabula import read_pdf
df = read_pdf('test.pdf', pages='all')
Documentation: https://github.com/chezou/tabula-py …
Thanks for the trick @Netchose!
![](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="144" height="144"><rect fill-opacity="0"/></svg>)
54 people are talking about this
Reading from the web
Want to read a JSON file from the web? Use read_json() to read it directly from a URL into a DataFrame!
See example
39 people are talking about this
Want to scrape a web page? Try read_html()!
Definitely worth trying before bringing out a more complex tool (Beautiful Soup, Selenium, etc.)
See example
64 people are talking about this
Are you scraping a webpage using read_html(), but it returns too many tables?
Use the 'match' parameter to find tables that contain a particular string!
See example
Thanks to @JrMontana08 for the trick!
39 people are talking about this
Creating example DataFrames
Need to create an example DataFrame? Here are 3 easy options:
pd.DataFrame({'col_one':[10, 20], 'col_two':[30, 40]})
pd.DataFrame(np.random.rand(2, 3), columns=list('abc'))
pd.util.testing.makeMixedDataFrame()
See output
16 people are talking about this
Need to create a DataFrame for testing?
pd.util.testing.makeDataFrame()
.makeMissingDataframe()
.makeTimeDataFrame()
.makeMixedDataFrame()
137 people are talking about this
Need to create a time series dataset for testing? Use pd.util.testing.makeTimeDataFrame()
Need more control over the columns & data? Generate data with np.random & overwrite index with makeDateIndex()
See example
31 people are talking about this
Creating columns
Want to create new columns (or overwrite existing columns) within a method chain? Use "assign"!
See example
22 people are talking about this
Need to create a bunch of new columns based on existing columns? Use this pattern:
for col in df.columns:
df[f'{col}_new'] = df[col].apply(my_function)
See example
Thanks to @pmbaumgartner for this trick!#Python #DataScience #pandas #pandastricks
65 people are talking about this
Need to remove a column from a DataFrame and store it as a separate Series? Use "pop"!
See example
36 people are talking about this
Want to insert a new column into a DataFrame at a specific location? Use the "insert" method:
df.insert(location, name, value)
See example
P.S. You can find the other 89 tricks here: https://www.dataschool.io/python-pandas-tips-and-tricks/ …#Python #DataScience #pandas #pandastricks
21 people are talking about this
Renaming columns
3 ways to rename columns:
1. Most flexible option:
df = df.rename({'A':'a', 'B':'b'}, axis='columns')
2. Overwrite all column names:
df.columns = ['a', 'b']
3. Apply string method:
df.columns = df.columns.str.lower()#Python #DataScience #pandastricks
19 people are talking about this
Add a prefix to all of your column names:
df.add_prefix('X_')
Add a suffix to all of your column names:
df.add_suffix('_Y')#Python #DataScience
39 people are talking about this
Need to rename all of your columns in the same way? Use a string method:
Replace spaces with _:
df.columns = df.columns.str.replace(' ', '_')
Make lowercase & remove trailing whitespace:
df.columns = df.columns.str.lower().str.rstrip()#Python #pandastricks
27 people are talking about this
Selecting rows and columns
You can use f-strings (Python 3.6+) when selecting a Series from a DataFrame!
See example
53 people are talking about this
Need to select multiple rows/columns? "loc" is usually the solution:
select a slice (inclusive):
df.loc[0:4, 'col_A':'col_D']
select a list:
df.loc[[0, 3], ['col_A', 'col_C']]
select by condition:
df.loc[df.col_A=='val', 'col_D']#Python #pandastricks
See Kevin Markham's other Tweets
"loc" selects by label, and "iloc" selects by position.
But what if you need to select by label *and* position? You can still use loc or iloc!
See example
P.S. Don't use "ix", it has been deprecated since 2017.#Python #DataScience #pandas #pandastricks
31 people are talking about this
Want to select from a DataFrame by label *and* position?
Most readable approach is to chain "loc" (selection by label) and "iloc" (selection by position).
See example
Thanks to @Dean_La for this trick!#Python #DataScience #pandas #pandastricks
21 people are talking about this
Reverse column order in a DataFrame:
df.loc[:, ::-1]
Reverse row order:
df.loc[::-1]
Reverse row order and reset the index:
df.loc[::-1].reset_index(drop=True)
Want more #pandastricks? Working on a video right now, stay tuned...#Python #DataScience
22 people are talking about this
Want to select multiple slices of columns from a DataFrame?
1. Use df.loc to select & pd.concat to combine
2. Slice df.columns & select using brackets
3. Use np.r_ to combine slices & df.iloc to select
See example
58 people are talking about this
Filtering rows by condition
Filter DataFrame by multiple OR conditions:
df[(df.color == 'red') | (df.color == 'green') | (df.color == 'blue')]
Shorter way:
df[df.color.isin(['red', 'green', 'blue'])]
Invert the filter:
df[~df.color.isin(['red', 'green', 'blue'])]#Python #pandastricks
27 people are talking about this
Want to know the *count* of rows that match a condition?
(condition).sum()
Want to know the *percentage* of rows that match a condition?
(condition).mean()
See example
88 people are talking about this
Want to filter a DataFrame to only include the largest categories?
1. Save the value_counts() output
2. Get the index of its head()
3. Use that index with isin() to filter the DataFrame
See example
61 people are talking about this
Want to combine the smaller categories in a Series into a single category called "Other"?
1. Save the index of the largest values of value_counts()
2. Use where() to replace all other values with "Other"
See example
80 people are talking about this
Want to combine the small categories in a Series (<10% frequency) into a single category?
1. Save the normalized value counts
2. Filter by frequency & save the index
3. Replace small categories with "Other"
See example
28 people are talking about this
Are you trying to filter a DataFrame using lots of criteria? It can be hard to writeand to read!
Instead, save the criteria as objects and use them to filter. Or, use reduce() to combine the criteria!
See example
90 people are talking about this
Want to filter a DataFrame that doesn't have a name?
Use the query() method to avoid creating an intermediate variable!
See example
31 people are talking about this
Need to refer to a local variable within a query() string? Just prefix it with the @ symbol!
See example
29 people are talking about this
If you want to use query() on a column name containing a space, just surround it with backticks! (New in pandas 0.25)
See example
22 people are talking about this
Manipulating strings
Want to concatenate two string columns?
Option 1: Use a string method
Option 2: Use plus signs
See example
Which option do you prefer, and why?#Python #DataScience #pandas #pandastricks
32 people are talking about this
Need to split a string into multiple columns? Use str.split() method, expand=True to return a DataFrame, and assign it to the original DataFrame.
See example
38 people are talking about this
Need to split names of variable length into first_name & last_name?
1. Use str.split(n=1) to split only once (returns a Series of lists)
2. Chain str[0] and str[1] on the end to select the list elements
See example
36 people are talking about this
Need to count the number of words in a Series? Just use a string method to count the spaces and add 1!
See example
34 people are talking about this
Working with data types
Numbers stored as strings? Try astype():
df.astype({'col1':'int', 'col2':'float'})
But it will fail if you have any invalid input. Better way:
df.apply(http://pd.to _numeric, errors='coerce')
Converts invalid input to NaN
21 people are talking about this
Select columns by data type:http://df.select _dtypes(include='number')http://df.select _dtypes(include=['number', 'category', 'object'])http://df.select _dtypes(exclude=['datetime', 'timedelta'])#Python #DataScience #pandas #pandastricks
19 people are talking about this
Want to save a *massive* amount of memory? Fix your data types:
More info: https://pythonspeed.com/articles/pandas-load-less-data/ … by @itamarst#Python #pandastricks
35 people are talking about this
Does your object column contain mixed data types? Use df.col.apply(type).value_counts() to check!
See example
Thanks to @chris1610 for inspiring this trick! Read more: https://pbpython.com/currency-cleanup.html …#Python #DataScience #pandas #pandastricks
34 people are talking about this
Need to clean an object column with mixed data types? Use "replace" (not str.replace) and regex!
See example
P.S. Not sure when to use "replace" versus "str.replace"? Read this: https://stackoverflow.com/a/56625032/1636598 …#Python #DataScience #pandas #pandastricks
35 people are talking about this
Two useful properties of ordered categories:
See example
15 people are talking about this
Problem: Your dataset has many columns and you want to ensure the correct data types
Solution:
1. Create CSV of column names & dtypes
2. Read it into a DF
3. Convert it to dict
4. Use dict to specify dtypes of dataset
61 people are talking about this
Encoding data
Need to convert a column from continuous to categorical? Use cut():
df['age_groups'] = pd.cut(df.age, bins=[0, 18, 65, 99], labels=['child', 'adult', 'elderly'])
0 to 18
18 to 65
65 to 99
165 people are talking about this
Need to convert a column from continuous to categorical?
See example
50 people are talking about this
Want to dummy encode (or "one hot encode") your DataFrame? Use pd.get_dummies(df) to encode all object & category columns.
Want to drop the first level since it provides redundant info? Set drop_first=True.
See example & read thread
32 people are talking about this
Three useful ways to convert one set of values to another:
1. map() using a dictionary
2. factorize() to encode each value as an integer
3. comparison statement to return boolean values
See example
68 people are talking about this
Need to apply the same mapping to multiple columns at once? Use "applymap" (DataFrame method) with "get" (dictionary method).
See example
58 people are talking about this
Extracting data from lists
Has your data ever been TRAPPED in a Series of Python lists?
Expand the Series into a DataFrame by using apply() and passing it the Series constructor
See example
28 people are talking about this
Do you have a Series containing lists of items? Create one row for each item using the "explode" method
New in pandas 0.25! See example#Python #DataScience #pandas #pandastricks
188 people are talking about this
Does your Series contain comma-separated items? Create one row for each item:"str.split" creates a list of strings
"assign" overwrites the existing column
See example
See Kevin Markham's other Tweets
You can also do the reverse! See example
Thanks to @EForEndeavour for this tip
20 people are talking about this
Working with time series data
If you need to create a single datetime column from multiple columns, you can use to_datetime()
See example
You must include: month, day, year
You can also include: hour, minute, second#Python #DataScience #pandas #pandastricks
54 people are talking about this
Want to convert "year" and "day of year" into a single datetime column?
1. Combine them into one number
2. Convert to datetime and specify its format
See example
List of all format codes: https://strftime.org/ #Python #DataScience #pandastricks
29 people are talking about this
One reason to use the datetime data type is that you can access many useful attributes via "dt", like:
df.column.dt.hour
Other attributes include: year, month, day, dayofyear, week, weekday, quarter, days_in_month...
See full list
25 people are talking about this
Need to perform an aggregation (sum, mean, etc) with a given frequency (monthly, yearly, etc)?
Use resample! It's like a "groupby" for time series data. See example
"Y" means yearly. See list of frequencies: https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#offset-aliases …#Python #pandastricks
27 people are talking about this
Problem: You have time series data that you want to aggregate by day, but you're only interested in weekends.
Solution:
1. resample by day ('D')
2. filter by day of week (5=Saturday, 6=Sunday)
See example
44 people are talking about this
Want to calculate the difference between each row and the previous row? Use df.col_name.diff()
Want to calculate the percentage change instead? Use df.col_name.pct_change()
See example
32 people are talking about this
Need to convert a datetime Series from UTC to another time zone?
1. Set current time zone
2. Convert
Automatically handles Daylight Savings Time!
See example
15 people are talking about this
Handling missing values
Calculate % of missing values in each column:
df.isna().mean()
Drop columns with any missing values:
df.dropna(axis='columns')
Drop columns in which more than 10% of values are missing:
df.dropna(thresh=len(df)*0.9, axis='columns')#Python #pandastricks
22 people are talking about this
Want to know the *count* of missing values in a DataFrame?
Just want to know if there are *any* missing values?
See example
39 people are talking about this
Need to fill missing values in your time series data? Use df.interpolate()
Defaults to linear interpolation, but many other methods are supported!
Want more pandas tricks? Watch this:https://www.youtube.com/watch?v=RlIiVeig3hc …
#Python #DataScience #pandas #pandastricks
29 people are talking about this
Do you need to store missing values ("NaN") in an integer Series? Use the "Int64" data type!
See example
(New in v0.24, API is experimental/subject to change)#Python #DataScience #pandas #pandastricks
21 people are talking about this
Using aggregation functions
Instead of aggregating by a single function (such as 'mean'), you can aggregate by multiple functions by using 'agg' (and passing it a list of functions) or by using 'describe' (for summary statistics
See example
21 people are talking about this
Did you know that "last" is an aggregation function, just like "sum" and "mean"?
Can be used with a groupby to extract the last value in each group. See example
P.S. You can also use "first" and "nth" functions!#Python #DataScience #pandas #pandastricks
31 people are talking about this
Are you applying multiple aggregations after a groupby? Try "named aggregation":
New in pandas 0.25! See example
33 people are talking about this
Are you applying multiple aggregations after a groupby? Try "named aggregation":
New in pandas 0.25! See example
99 people are talking about this
Want to combine the output of an aggregation with the original DataFrame?
Instead of: df.groupby('col1').col2.func()
Use: df.groupby('col1').col2.transform(func)
"transform" changes the output shape
See example
59 people are talking about this
Using cumulative functions
Need to calculate a running total (or "cumulative sum")? Use the cumsum() function! Also works with groupby()
See example
Other cumulative functions: cummax(), cummin(), cumprod()#Python #DataScience #pandas #pandastricks
27 people are talking about this
Need to calculate a running count within groups? Do this:
df.groupby('col').cumcount() + 1
See example
Thanks to @kjbird15 and @EForEndeavour for this trick!
32 people are talking about this
Random sampling
Randomly sample rows from a DataFrame:
df.sample(n=10)
df.sample(frac=0.25)
Useful parameters:
See Kevin Markham's other Tweets
Want to shuffle your DataFrame rows?
df.sample(frac=1, random_state=0)
Want to reset the index after shuffling?
df.sample(frac=1, random_state=0).reset_index(drop=True)#Python #DataScience #pandas #pandastricks
26 people are talking about this
Split a DataFrame into two random subsets:
df_1 = df.sample(frac=0.75, random_state=42)
df_2 = df.drop(df_1.index)
(Only works if df's index values are unique)
P.S. Working on a video of my 25 best #pandastricks, stay tuned!
See Kevin Markham's other Tweets
Merging DataFrames
When you are merging DataFrames, you can identify the source of each row (left/right/both) by setting indicator=True.
See example
P.S. Learn 25 more #pandastricks in 25 minutes: https://www.youtube.com/watch?v=RlIiVeig3hc …#Python #DataScience #pandas
21 people are talking about this
Merging datasets? Check that merge keys are unique in BOTH datasets:
pd.merge(left, right, validate='one_to_one')
16 people are talking about this
Styling DataFrames
Two simple ways to style a DataFrame:
See example
For more style options, watch trick #25: https://www.youtube.com/watch?v=RlIiVeig3hc …
30 people are talking about this
Want to add formatting to your DataFrame? For example:
- hide the index
- add a caption
- format numbers & dates
- highlight min & max values
Watch
Code: https://nbviewer.jupyter.org/github/justmarkham/pandas-videos/blob/master/top_25_pandas_tricks.ipynb …
25 more tricks: https://www.youtube.com/watch?v=RlIiVeig3hc …#Python #pandastricks
36 people are talking about this
Exploring a dataset
Want to explore a new dataset without too much work?
1. Pick one:
2. import pandas_profiling
3. df.profile_report()
4.
See example
377 people are talking about this
Need to check if two Series contain the same elements?
df.A == df.B
df.A.equals(df.B)
df.equals(df2)
equals() properly handles NaNs, whereas == does not#Python #DataScience #pandas #pandastricks
35 people are talking about this
Need to check if two Series are "similar"? Use this:
pd.testing.assert_series_equal(df.A, df.B, ...)
Useful arguments include:
See example
24 people are talking about this
My favorite feature in pandas 0.25: If DataFrame has more than 60 rows, only show 10 rows (saves your screen space!)
You can modify this: pd.set_option('min_rows', 4)
See example
More info: https://pandas.pydata.org/pandas-docs/stable/whatsnew/v0.25.0.html#shorter-truncated-repr-for-series-and-dataframe …#Python #DataScience #pandastricks
30 people are talking about this
Want to examine the "head" of a wide DataFrame, but can't see all of the columns?
Solution #1: Change display options to show all columns
Solution #2: Transpose the head (swaps rows and columns)
See example
36 people are talking about this
Want to plot a DataFrame? It's as easy as:
df.plot(kind='...')
You can use:
line
bar
barh
hist
box
kde
area
scatter
hexbin
pie
Other plot types are available via pd.plotting!
Examples: https://pandas.pydata.org/pandas-docs/stable/user_guide/visualization.html …#Python #dataviz #pandastricks
27 people are talking about this
Want to create interactive plots using pandas 0.25?
1. Pick one:
2. pd.options.plotting.backend = 'hvplot'
3. df.plot(...)
4.
See example
183 people are talking about this
Handling warnings
Did you encounter the dreaded SettingWithCopyWarning?
The usual solution is to rewrite your assignment using "loc":
See example
32 people are talking about this
Did you get a "SettingWithCopyWarning" when creating a new column? You are probably assigning to a DataFrame that was created from another DataFrame.
Solution: Use the "copy" method when copying a DataFrame!
See example
31 people are talking about this
Other
Goal: Rearrange the columns in your DataFrame
Options:
1. Specify all column names in desired order
2. Specify columns to move, followed by remaining columns
3. Specify column positions in desired order
See example
25 people are talking about this
Problem: Your DataFrame is in "wide format" (lots of columns), but you need it in "long format" (lots of rows)
Solution: Use melt()!
See example
Long format is better for analysis, transformation, merges...#Python #DataScience #pandastricks
63 people are talking about this
If you've created a groupby object, you can access any of the groups (as a DataFrame) using the get_group() method.
See example
99 people are talking about this
Do you have a Series with a MultiIndex?
Reshape it into a DataFrame using the unstack() method. It's easier to read, plus you can interact with it using DataFrame methods!
See example
P.S. Want a video with my top 25 #pandastricks?
21 people are talking about this
pandas trick:
There are many display options you can change:
max_rows
max_columns
max_colwidth
precision
date_dayfirst
date_yearfirst
How to use:
pd.set_option('display.max_rows', 80)
pd.reset_option('display.max_rows')
See all:
pd.describe_option()#Python #pandastricks
18 people are talking about this
Show total memory usage of a DataFrame:http://df.info (memory_usage='deep')
Show memory used by each column:
df.memory_usage(deep=True)
Need to reduce? Drop unused columns, or convert object columns to 'category' type.#Python #pandas #pandastricks
31 people are talking about this
Need to know which version of pandas you're using?
Need to know the versions of its dependencies (numpy, matplotlib, etc)?
Helpful when reading the documentation!#Python #pandas #pandastricks
See Kevin Markham's other Tweets
Want to use NumPy without importing it? You can access ALL of its functionality from within pandas! See example
This is probably *not* a good idea since it breaks with a long-standing convention. But it's a neat trick
42 people are talking about this